﻿using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using CodeGenerator.Models;
using GeneratorModels.Utility;
using MySql.Data.MySqlClient;

namespace GeneratorModels.Models
{
    internal class MysqlDataBaseDAL : DataBaseDAL
    {
        internal override List<TableInfo> GetAllTableName(string ConnectionString, string DataBaseName)
        {
            var strSql = new StringBuilder();
            strSql.Append(
                "SELECT TABLE_NAME,TABLE_TYPE,`ENGINE`,TABLE_COMMENT FROM information_schema.`TABLES` WHERE TABLE_SCHEMA=@DataBaseName");
            MySqlParameter[] parameters =
            {
                new MySqlParameter("@DataBaseName", MySqlDbType.String, 64)
            };
            parameters[0].Value = DataBaseName;
            var tableNames = new List<TableInfo>();
            TableInfo tableInfo = null;
            using (
                MySqlDataReader rdr = MySqlHelperUtil.ExecuteReader(ConnectionString, CommandType.Text,
                    strSql.ToString(), parameters))
            {
                while (rdr.Read())
                {
                    tableInfo = new TableInfo
                    {
                        TABLE_NAME = MySqlHelperUtil.GetString(rdr, "TABLE_NAME"),
                        TABLE_TYPE = MySqlHelperUtil.GetString(rdr, "TABLE_TYPE"),
                        ENGINE = MySqlHelperUtil.GetString(rdr, "ENGINE"),
                        TABLE_COMMENT = MySqlHelperUtil.GetString(rdr, "TABLE_COMMENT")
                    };
                    tableNames.Add(tableInfo);
                }
            }
            return tableNames.OrderBy(a => a.TABLE_NAME).ToList();
        }

        internal override List<ColumnInfo> GetAllColumns(string ConnectionString, string DataBaseName, string TableName)
        {
            var strSql = new StringBuilder();
            strSql.Append(
                "SELECT COLUMN_NAME,ORDINAL_POSITION,COLUMN_DEFAULT,IS_NULLABLE,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,COLUMN_TYPE,COLUMN_KEY,EXTRA,COLUMN_COMMENT FROM information_schema.`COLUMNS` WHERE TABLE_SCHEMA=@DataBaseName AND TABLE_NAME=@TableName;");
            MySqlParameter[] parameters =
            {
                new MySqlParameter("@DataBaseName", MySqlDbType.String, 64),
                new MySqlParameter("@TableName", MySqlDbType.String, 64)
            };
            parameters[0].Value = DataBaseName;
            parameters[1].Value = TableName;
            var columnInfos = new List<ColumnInfo>();
            ColumnInfo columnInfo = null;
            using (
                MySqlDataReader rdr = MySqlHelperUtil.ExecuteReader(ConnectionString, CommandType.Text,
                    strSql.ToString(), parameters))
            {
                while (rdr.Read())
                {
                    columnInfo = new ColumnInfo
                    {
                        COLUMN_NAME = MySqlHelperUtil.GetString(rdr, "COLUMN_NAME"),
                        COLUMN_NAME_Case = MySqlHelperUtil.GetString(rdr, "COLUMN_NAME"),
                        ORDINAL_POSITION = MySqlHelperUtil.GetInt(rdr, "ORDINAL_POSITION"),
                        COLUMN_DEFAULT = MySqlHelperUtil.GetString(rdr, "COLUMN_DEFAULT"),
                        IS_NULLABLE = MySqlHelperUtil.GetString(rdr, "IS_NULLABLE"),
                        DATA_TYPE = MySqlHelperUtil.GetString(rdr, "DATA_TYPE"),
                        CHARACTER_MAXIMUM_LENGTH = MySqlHelperUtil.GetInt64(rdr, "CHARACTER_MAXIMUM_LENGTH"),
                        COLUMN_TYPE = MySqlHelperUtil.GetString(rdr, "COLUMN_TYPE"),
                        COLUMN_KEY = MySqlHelperUtil.GetString(rdr, "COLUMN_KEY"),
                        EXTRA = MySqlHelperUtil.GetString(rdr, "EXTRA"),
                        COLUMN_COMMENT = MySqlHelperUtil.GetString(rdr, "COLUMN_COMMENT")
                    };
                    char c = columnInfo.COLUMN_NAME_Case.Substring(0, 1).ToArray()[0];
                    if (c < 65 || c > 90)
                    {
                        columnInfo.COLUMN_NAME_Case = UtilityMethod.ToTitleCase(columnInfo.COLUMN_NAME_Case);
                    }
                    columnInfos.Add(columnInfo);
                }
            }
            return columnInfos;
        }

        internal override List<IndexInfo> GetAllIndexs(string ConnectionString, string DataBaseName, string TableName)
        {
            var strSql = new StringBuilder();
            strSql.Append(
                "SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE table_name = @TableName AND table_schema = @DataBaseName ;");
            MySqlParameter[] parameters =
            {
                new MySqlParameter("@DataBaseName", MySqlDbType.String, 64),
                new MySqlParameter("@TableName", MySqlDbType.String, 64)
            };
            parameters[0].Value = DataBaseName;
            parameters[1].Value = TableName;
            var indexInfos = new List<IndexInfo>();
            IndexInfo indexInfo = null;
            using (
                MySqlDataReader rdr = MySqlHelperUtil.ExecuteReader(ConnectionString, CommandType.Text,
                    strSql.ToString(), parameters))
            {
                while (rdr.Read())
                {
                    indexInfo = new IndexInfo
                    {
                        NON_UNIQUE = MySqlHelperUtil.GetInt(rdr, "NON_UNIQUE"),
                        INDEX_NAME = MySqlHelperUtil.GetString(rdr, "INDEX_NAME"),
                        SEQ_IN_INDEX = MySqlHelperUtil.GetInt(rdr, "SEQ_IN_INDEX"),
                        COLUMN_NAME = MySqlHelperUtil.GetString(rdr, "COLUMN_NAME"),
                        COLLATION = MySqlHelperUtil.GetString(rdr, "COLLATION"),
                        CARDINALITY = MySqlHelperUtil.GetInt(rdr, "CARDINALITY"),
                        INDEX_TYPE = MySqlHelperUtil.GetString(rdr, "INDEX_TYPE"),
                        COMMENT = MySqlHelperUtil.GetString(rdr, "COMMENT"),
                        INDEX_COMMENT = MySqlHelperUtil.GetString(rdr, "INDEX_COMMENT")
                    };
                    indexInfos.Add(indexInfo);
                }
            }
            return indexInfos;
        }
    }
}